﻿// YellowJExcel.cs
using ClosedXML.Excel;
using DocumentFormat.OpenXml.Spreadsheet;
using MiniExcelLibs;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;

namespace YellowJExcel
{
    /// <summary>
    /// YellowJExcel - 高性能Excel操作库
    /// 特点：高性能读写、简洁API、灵活样式控制
    /// </summary>
    public static class YellowJExcel
    {
        #region 核心基本功能

        /// <summary>
        /// 从Excel文件读取数据（无需配置）
        /// </summary>
        /// <typeparam name="T">目标类型</typeparam>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>数据列表</returns>
        public static List<T> Read<T>(string filePath) where T : class, new()
        {
            if (!System.IO.File.Exists(filePath))
                throw new FileNotFoundException($"文件未找到: {filePath}");
            return MiniExcel.Query<T>(filePath).ToList();
        }

        /// <summary>
        /// 从Excel文件读取数据（基于MiniExcel的高性能读取）
        /// </summary>
        /// <typeparam name="T">目标类型</typeparam>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="sheetName">工作表名（可选）</param>
        /// <returns>数据列表</returns>
        public static List<T> Read<T>(string filePath, string sheetName = null) where T : class, new()
        {
            if (!System.IO.File.Exists(filePath))
                throw new FileNotFoundException($"文件未找到: {filePath}");
            return MiniExcel.Query<T>(filePath, sheetName: sheetName).ToList();
        }

        /// <summary>
        /// 写入多个工作表到Excel文件（结合MiniExcel性能和ClosedXML样式）
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="filePath">输出文件路径</param>
        /// <param name="sheets">工作表数据集合，Key为数据列表，Value为对应的配置</param>
        public static void Write<T>(string filePath, Dictionary<List<T>, ExcelConfig<T>> sheets) where T : class
        {
            using var workbook = new XLWorkbook();

            foreach (var sheet in sheets)
            {
                var data = sheet.Key;
                var config = sheet.Value ?? new ExcelConfig<T>();

                var worksheet = workbook.Worksheets.Add(config.SheetName ?? "Sheet" + (workbook.Worksheets.Count + 1));

                // 获取要导出的属性（排除配置中指定的列）
                var props = typeof(T).GetProperties()
                    .Where(p => !config.ExcludedColumns.Contains(p.Name))
                    .ToList();

                // 如果配置了列顺序，则按照配置的顺序排列列
                if (config.ColumnOrder?.Any() == true)
                {
                    props = config.ColumnOrder
                        .Select(name => props.FirstOrDefault(p => p.Name == name))
                        .Where(p => p != null)
                        .ToList();
                }

                // 写入表头
                for (int i = 0; i < props.Count; i++)
                {
                    var prop = props[i];
                    var cell = worksheet.Cell(1, i + 1);

                    // 优先读取 [ExcelColumnName] 特性作为列名，如果没有则使用属性名
                    var colName = prop.GetCustomAttribute(typeof(MiniExcelLibs.Attributes.ExcelColumnNameAttribute)) is MiniExcelLibs.Attributes.ExcelColumnNameAttribute attr
                        ? attr.ExcelColumnName
                        : prop.Name;

                    cell.Value = colName;

                    // 设置默认橘黄色表头样式
                    cell.Style.Fill.BackgroundColor = XLColor.FromHtml("#FFA500");
                    cell.Style.Font.Bold = true;

                    // 添加表头边框
                    cell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                }

                // 写入数据
                for (int row = 0; row < data.Count; row++)
                {
                    var item = data[row];
                    for (int col = 0; col < props.Count; col++)
                    {
                        var cell = worksheet.Cell(row + 2, col + 1);
                        cell.Value = props[col].GetValue(item)?.ToString() ?? "";

                        // 应用列背景色
                        if (config.ColumnColors.TryGetValue(props[col].Name, out var color))
                        {
                            cell.Style.Fill.BackgroundColor = XLColor.FromHtml(color);
                        }

                        // 应用条件样式
                        foreach (var rule in config.ConditionalRules)
                        {
                            if (rule.Condition(item))
                            {
                                cell.Style.Fill.BackgroundColor = XLColor.FromHtml(rule.Color);
                            }
                        }

                        // 为所有有数据的单元格添加边框
                        if (!string.IsNullOrEmpty(cell.Value.ToString()))
                        {
                            cell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        }
                    }
                }

                // 设置隐藏列
                foreach (var colName in config.HiddenColumns)
                {
                    var colIndex = props.FindIndex(p => p.Name == colName) + 1;
                    if (colIndex > 0)
                    {
                        worksheet.Column(colIndex).Hide();
                    }
                }

                // 设置整个数据区域边框（包含表头和数据）
                var totalRows = data.Count + 1; // 包含表头
                var totalCols = props.Count;
                var range = worksheet.Range(1, 1, totalRows, totalCols);
                range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;

                // 自动调整列宽
                worksheet.Columns().AdjustToContents();

                // 在写入数据后，处理需要合并的列
                if (config.MergeColumns?.Any() == true)
                {
                    foreach (var colName in config.MergeColumns)
                    {
                        var colIndex = props.FindIndex(p => p.Name == colName) + 1;
                        if (colIndex > 0)
                        {
                            var currentValue = "";
                            var startRow = 2; // 从第二行开始（数据行）

                            for (int row = 2; row <= data.Count + 1; row++)
                            {
                                var cellValue = worksheet.Cell(row, colIndex).Value.ToString();

                                // 当值发生变化或到达最后一行时处理合并
                                if (currentValue != cellValue)
                                {
                                    // 如果有需要合并的单元格（连续相同值的单元格数量大于1）
                                    if (row - startRow > 1)
                                    {
                                        // 合并相同值的单元格
                                        var mergeRange1 = worksheet.Range(startRow, colIndex, row - 1, colIndex);
                                        mergeRange1.Merge();
                                        mergeRange1.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                                    }

                                    currentValue = cellValue;
                                    startRow = row;
                                }
                                // 特别处理最后一行的情况
                                else if (row == data.Count + 1 && row - startRow >= 1)
                                {
                                    // 合并从startRow到最后一行的单元格
                                    var mergeRange2 = worksheet.Range(startRow, colIndex, row, colIndex);
                                    mergeRange2.Merge();
                                    mergeRange2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                                }
                            }
                        }
                    }
                }
            }

            workbook.SaveAs(filePath);
        }

        /// <summary>
        /// 写入Excel文件（结合MiniExcel性能和ClosedXML样式）
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="filePath">输出文件路径</param>
        /// <param name="data">数据列表</param>
        /// <param name="config">导出配置</param>
        public static void Write<T>(string filePath, List<T> data, ExcelConfig<T> config = null) where T : class
        {
            Write(filePath, new Dictionary<List<T>, ExcelConfig<T>> { { data, config } });
        }
        #endregion

        #region 扩展功能

        /// <summary>
        /// 合并多个Excel文件到一个文件中
        /// </summary>
        /// <param name="targetPath">目标文件路径</param>
        /// <param name="sourceFiles">源文件路径列表</param>
        public static void MergeFiles(string targetPath, List<string> sourceFiles)
        {
            using var targetWorkbook = new XLWorkbook();

            foreach (var sourceFile in sourceFiles)
            {
                using var sourceWorkbook = new XLWorkbook(sourceFile);
                foreach (var worksheet in sourceWorkbook.Worksheets)
                {
                    var newSheetName = worksheet.Name;
                    var count = 1;
                    // 防止工作表名称重复
                    while (targetWorkbook.Worksheets.Contains(newSheetName))
                    {
                        newSheetName = $"{worksheet.Name}_{count++}";
                    }
                    worksheet.CopyTo(targetWorkbook, newSheetName);
                }
            }

            targetWorkbook.SaveAs(targetPath);
        }
        #endregion
    }

    /// <summary>
    /// Excel导出配置类
    /// 用于配置Excel导出时的各种样式和格式选项
    /// </summary>
    public class ExcelConfig<T>
    {
        /// <summary>
        /// 工作表名称
        /// 默认值为"Sheet1"
        /// </summary>
        public string SheetName { get; set; } = "Sheet1";

        /// <summary>
        /// 要排除的列（不会导出这些列）
        /// 使用HashSet提高查找性能
        /// </summary>
        public HashSet<string> ExcludedColumns { get; set; } = new HashSet<string>();

        /// <summary>
        /// 要隐藏的列（导出但默认隐藏）
        /// </summary>
        public HashSet<string> HiddenColumns { get; set; } = new HashSet<string>();

        /// <summary>
        /// 指定列的显示顺序
        /// 按照列表中的顺序排列列，未在列表中的列将不会显示
        /// </summary>
        public List<string> ColumnOrder { get; set; } = new List<string>();

        /// <summary>
        /// 列背景色配置 (属性名 -> 十六进制颜色代码)
        /// 例如: {"Name", "#FF0000"} 将Name列背景色设置为红色
        /// </summary>
        public Dictionary<string, string> ColumnColors { get; set; } = new Dictionary<string, string>();

        /// <summary>
        /// 条件样式规则列表
        /// 每个规则包含一个条件函数和颜色代码
        /// 当条件函数返回true时，该行所有单元格应用指定颜色
        /// </summary>
        public List<(Func<T, bool> Condition, string Color)> ConditionalRules { get; set; } = new List<(Func<T, bool>, string)>();

        /// <summary>
        /// 需要合并重复值的列名集合
        /// 对于连续相同的值，将合并为一个单元格
        /// </summary>
        public HashSet<string> MergeColumns { get; set; } = new HashSet<string>();
    }
}